/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Entity.Project;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author THANHDAT
 */
public class DAO_Project {

    public DAO_Project() {
    }

    public static List<Project> showProject() {
        //String query = "select * from tblProjects";
        String querry = "SELECT * FROM tblOrders INNER JOIN tblOrderStatus ON tblOrders.OrderId = tblOrderStatus.orderStaID";
        List<Project> lstPro = new ArrayList<Project>();
        try {
            Statement st = DAO_Connect.getConnection().createStatement();
            ResultSet rs = st.executeQuery(querry);
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int cateId = rs.getInt(3);
                String shortDes = rs.getString(4);
                String des = rs.getString(5);
                String image = rs.getString(6);
                Project pro = new Project(id, name, cateId, shortDes, des, image);
                lstPro.add(pro);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lstPro;
    }

    public List<Project> detail(int id) throws SQLException {
        List<Project> lstPro = new ArrayList<Project>();
        String query = "select * from tblProjects where catId = " + id;
        Statement st = DAO_Connect.getConnection().createStatement();
        ResultSet rs = st.executeQuery(query);
        while (rs.next()) {
            int proid = rs.getInt(1);
            String name = rs.getString(2);
            int cateId = rs.getInt(3);
            String shortDes = rs.getString(4);
            String des = rs.getString(5);
            String image = rs.getString(6);
            Project pro = new Project(proid, name, cateId, shortDes, des, image);
            lstPro.add(pro);
        }
        return lstPro;
    }

    public Project getProjectById(int proId) {
        String query = "select * from tblProjects where projectID = " + proId;
        Project pro = new Project();
        try {
            Statement st = DAO_Connect.getConnection().createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                pro.setId(rs.getInt(1));
                pro.setName(rs.getString(2));
                pro.setCategory(rs.getInt(3));
                pro.setImage(rs.getString(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pro;
    }

    public void addProject(String name, int cate, String shortDes, String des, String image) {
        String query = "INSERT INTO tblProjects VALUES (?,?,?,?,?)";
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement cstm = null;
        conn = DAO_Connect.getConnection();
        try {
            cstm = conn.prepareCall(query);
            cstm.setString(2, name);
            cstm.setInt(3, cate);
            cstm.setString(4, shortDes);
            cstm.setString(5, des);
            cstm.setString(6, image);
            rs = cstm.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
